Understanding What-If Analysis Tools in Excel

Course: Advanced Excel

Topic: Understanding What-If Analysis Tools in Excel

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Official Website: JustAcademy

1. Introduction to What-If Analysis

What-If Analysis is an advanced Excel technique used to examine how changing input values affects the results of formulas. It allows users to test different assumptions, compare possible outcomes, and make better-informed business decisions without manually rebuilding a worksheet for every situation.

For example, a business may want to understand what happens to its profit if product prices increase by 10%, sales volume decreases by 5%, or operating expenses rise. Instead of repeatedly editing formulas, Excel provides tools that help users evaluate these possibilities systematically.

What-If Analysis is especially useful in budgeting, sales forecasting, financial planning, investment calculations, inventory management, pricing decisions, and business performance analysis.

2. Learning Objectives

After studying this topic, learners should be able to:

• Understand the purpose of What-If Analysis in Excel.
• Identify input cells, formula cells, and target results.
• Use Goal Seek to find an input required to achieve a desired result.
• Use Scenario Manager to compare different sets of assumptions.
• Use one-variable and two-variable Data Tables to evaluate multiple outcomes.
• Understand the difference between Goal Seek, Scenario Manager, and Data Tables.
• Recognize when Solver may be more suitable than standard What-If Analysis tools.
• Apply What-If Analysis to practical sales, revenue, cost, and profit problems.
• Validate results and document assumptions before making decisions.

3. Why Is What-If Analysis Important?

Businesses frequently make decisions when some future values are uncertain. Actual sales, costs, selling prices, customer demand, and interest rates may differ from initial estimates. What-If Analysis helps evaluate the possible effects of these changes.

Common business questions include:

• What will happen to profit if sales increase by 15%?
• How many units must be sold to achieve a target profit?
• What selling price is required to reach a particular revenue target?
• How will higher material costs affect total expenses?
• What happens to loan payments when interest rates change?
• Which budget assumptions create the best or worst financial outcome?
• How sensitive is the final result to changes in price and sales volume?

4. Basic Components of What-If Analysis

Before using the tools, understand the three important components of a typical Excel model.

For example, suppose a company sells 100 units at ₹500 per unit and incurs a total cost of ₹35,000.

The revenue is ₹50,000 and the profit is ₹15,000. If the selling price, units sold, or cost changes, the formulas calculate the revised outcome automatically.

5. Main What-If Analysis Tools in Excel

Excel provides three primary What-If Analysis tools: Goal Seek, Scenario Manager, and Data Tables. Each tool answers a different type of question.

These tools are available from the Data tab under What-If Analysis in supported desktop versions of Excel. Menu locations and feature availability can vary by version and platform.

6. Understanding Goal Seek

Goal Seek is used when you know the result you want but do not know which input value will produce that result. Excel adjusts one input cell until the formula in the target cell reaches the specified value.

For example, a company currently earns ₹15,000 profit. Management wants to achieve ₹25,000 profit and needs to determine how many units must be sold at a fixed selling price and fixed total cost.

Example worksheet

Current profit is calculated as:

Revenue = Units Sold * Selling Price
Revenue = 100 * 500
Revenue = ₹50,000

Profit = Revenue - Total Cost
Profit = ₹50,000 - ₹35,000
Profit = ₹15,000

Steps to use Goal Seek

1. Enter the example data and formulas into the worksheet.
2. Select the Data tab.
3. Choose What-If Analysis > Goal Seek.
4. In the Set cell field, enter B6, the cell containing the profit formula.
5. In the To value field, enter 25000.
6. In the By changing cell field, enter B2, the Units Sold input cell.
7. Click OK and review the calculated value.
8. Keep the result if it meets your requirement, or restore the original value if needed.

In this example, Goal Seek finds 120 units because 120 × ₹500 − ₹35,000 = ₹25,000.

Important: The target cell must contain a formula that depends on the changing input cell. Goal Seek adjusts only one input value at a time and does not automatically determine the best solution across multiple independent variables.

7. Practical Uses of Goal Seek

Finding the required sales quantity

If selling price and total cost are known, Goal Seek can calculate how many units must be sold to achieve a profit target.

Finding the required selling price

If sales volume and total cost are fixed, Goal Seek can estimate the selling price required to achieve a target profit.

Finding the required revenue

A financial model can use Goal Seek to determine the revenue required to achieve a desired profit, given a cost assumption.

Loan and payment calculations

Goal Seek can adjust a single input in a loan payment formula, such as an interest rate, to reach a specified payment result.

Limitations of Goal Seek

• It adjusts only one changing cell per calculation.
• It works with a formula result that depends on the changing cell.
• The result depends on the formula and assumptions in the worksheet.
• It may not be suitable when the model has multiple constraints or several independent decision variables.
• The result should be checked for business feasibility, such as whole units, minimum prices, and capacity limits.

8. Understanding Scenario Manager

Scenario Manager lets you save multiple sets of input values and switch between them to compare their results. It is useful when several variables may change together.

For example, a business may create three budget scenarios:

• Optimistic: Higher sales and controlled costs.
• Expected: Sales and costs close to the current forecast.
• Pessimistic: Lower sales and higher costs.

Each scenario contains a set of values for selected changing cells. Excel substitutes those values into the worksheet model so the formula results can be compared.

Example financial model

The current profit is ₹40,000.

Example scenarios

The values are illustrative. The model calculates profit by subtracting operating costs from sales revenue.

Steps to create scenarios

1. Prepare the worksheet with input cells and result formulas.
2. Open the Data tab.
3. Select What-If Analysis > Scenario Manager.
4. Click Add to create a new scenario.
5. Enter a name such as Optimistic.
6. Specify the changing cells, such as B2:B3.
7. Enter the sales revenue and cost values for that scenario.
8. Repeat the process for Expected and Pessimistic scenarios.
9. Select a scenario and click Show to display its values in the worksheet.
10. Use Summary to create a Scenario Summary report comparing the outcomes.

Scenario Summary Report

A Scenario Summary report presents the input assumptions and selected result cells for different scenarios in a new worksheet. It makes it easier to compare the expected financial impact of each case.

Note: Scenario Summary reports are snapshots and do not automatically update when scenario values change. Generate a new report when updated scenario values need to be reflected.

9. Advantages and Limitations of Scenario Manager

Advantages

• Stores named sets of assumptions for future use.
• Allows users to compare best-case, expected, and worst-case situations.
• Can change multiple input values together.
• Helps with financial planning, budgeting, and risk assessment.
• Creates summary reports for comparing selected outcomes.

Limitations

• Each scenario supports up to 32 changing values.
• Many scenarios can become difficult to manage without clear naming.
• Scenarios do not automatically establish which outcome is most likely.
• Summary reports need to be recreated when scenario values change.
• Results are only as reliable as the assumptions and formulas used.

10. Understanding Data Tables

A Data Table is a What-If Analysis tool that calculates the results of a formula for different values of one or two input variables. It displays the possible outcomes together, making it easier to compare alternatives.

For example, a business may want to see how profit changes when selling prices vary from ₹400 to ₹600. A two-variable Data Table can also compare multiple prices against different sales volumes.

Data Tables are especially useful for sensitivity analysis, where users examine how changes in input assumptions affect a financial or operational result.

11. One-Variable Data Table

A one-variable Data Table tests multiple values for a single input cell while calculating the output of a formula.

Example: Testing different selling prices

Assume the following model:

Now test several prices to compare the resulting profit.

Steps to create a one-variable Data Table

1. Build the model with the input cell and formula output.
2. Enter a list of test prices in a column, for example D3:D7.
3. In the cell one row above and one column to the right of the first test value, enter a reference to the formula output, such as =B5 in E2, adjusting the layout as needed.
4. Select the complete table range, including the formula reference and test values.
5. Open Data > What-If Analysis > Data Table.
6. Because the test values are arranged vertically, enter the model's selling-price input cell in the Column input cell field.
7. Leave the Row input cell field blank.
8. Click OK and review the calculated outputs.

Exact cell positions depend on the worksheet layout. The important requirement is that the table includes a reference to the output formula and that the input-cell reference matches the orientation of the test values.

12. Two-Variable Data Table

A two-variable Data Table tests combinations of two input variables. It is useful when the output depends on two changing assumptions, such as selling price and units sold.

Example: Comparing price and sales volume

Use the following profit formula:

Profit = Units Sold * Selling Price - Total Cost

Assume total cost remains fixed at ₹35,000 for this simplified example.

The table shows how profit changes when both units sold and selling price change. In a realistic business model, costs may also change with volume, so the fixed-cost assumption should be reviewed before applying these results to real decisions.

Steps to create a two-variable Data Table

1. Prepare the input cells for Units Sold and Selling Price.
2. Create the formula that calculates profit from both input cells.
3. Place a reference to the profit formula in the top-left corner of the table layout.
4. Enter the different selling prices horizontally across the top row.
5. Enter the different sales volumes vertically down the first column.
6. Select the entire table, including the formula reference, row values, and column values.
7. Open Data > What-If Analysis > Data Table.
8. Set the Row input cell to the selling-price input cell because prices are listed horizontally.
9. Set the Column input cell to the units-sold input cell because quantities are listed vertically.
10. Click OK and review the profit values for each combination.

Always confirm the row and column input-cell assignments. Reversing them can produce misleading results or an incorrect table.

13. Comparing Goal Seek, Scenario Manager, and Data Tables

14. What Is Solver in Excel?

Solver is a separate Excel add-in that can find an optimal solution by changing multiple decision variables while respecting specified constraints. It is related to optimization, rather than being one of the three primary What-If Analysis tools.

For example, a company might want to maximize profit while staying within limits on labor hours, available materials, production capacity, and budget. Solver can adjust several decision variables to find a solution that satisfies the constraints.

Use Goal Seek when adjusting one input to achieve a specific formula result. Consider Solver when the problem involves several changing cells, constraints, or an objective such as maximizing profit or minimizing cost.

15. Practical Business Applications

Sales forecasting

Test different assumptions for sales volume, selling price, or demand to understand their impact on revenue and profit forecasts.

Budget planning

Compare optimistic, expected, and pessimistic expense or revenue assumptions to estimate possible budget outcomes.

Pricing decisions

Use Data Tables to examine how alternative prices affect revenue and profit. Include realistic assumptions about demand, discounts, and variable costs.

Break-even analysis

Use Goal Seek to find the input required to reach a zero-profit or target-profit result when the model formula supports that calculation.

Financial planning

Test different interest rates, loan periods, and principal amounts using appropriate formulas and Data Tables.

Inventory management

Model different demand levels and purchase costs to understand their effects on inventory expenses and projected profit.

16. Best Practices for What-If Analysis

• Keep input cells, formulas, and output cells clearly separated.
• Label every assumption so users know what is changing.
• Use realistic input values based on available evidence.
• Check that formulas reference the intended cells.
• Use Goal Seek for a single changing input and a known target result.
• Use Scenario Manager when several assumptions change together.
• Use Data Tables when comparing many values for one or two variables.
• Use Solver for appropriate multi-variable optimization problems with constraints.
• Keep an untouched copy of the original workbook before testing major changes.
• Record the assumptions and units used in the analysis.
• Review whether the results are practical and achievable.
• Verify important results independently before using them in business decisions.

17. Common Errors and Troubleshooting

18. Practical Project: Sales Profit What-If Analysis

Project objective: Create an Excel workbook that helps a business understand how different sales assumptions affect revenue and profit.

Project requirements

• Create input cells for units sold, selling price, and total cost.
• Calculate revenue and profit using formulas.
• Use Goal Seek to find the number of units needed for a target profit.
• Create Optimistic, Expected, and Pessimistic scenarios with different sales and cost assumptions.
• Generate a Scenario Summary report comparing the cases.
• Create a one-variable Data Table to test several selling prices.
• Create a two-variable Data Table to compare selling prices and sales volumes.
• Format the outputs as currency and label all assumptions clearly.
• Write a short conclusion describing which inputs have the greatest effect on profit.

Expected deliverables

1. Model worksheet: Input cells and formulas for revenue and profit.
2. Goal Seek result: Required units or price for the target profit.
3. Scenario Summary: Comparison of saved business cases.
4. One-variable Data Table: Profit results across different prices.
5. Two-variable Data Table: Profit results across different price and volume combinations.
6. Conclusion: A brief explanation of assumptions, findings, and limitations.

19. Practice Exercises

1. A company sells 200 units at ₹300 each and incurs ₹40,000 in total cost. Calculate its revenue and profit.
2. Use Goal Seek to determine the number of units required to achieve a profit of ₹30,000, assuming a fixed selling price and fixed total cost.
3. Create three scenarios for a business with different revenue and expense assumptions.
4. Generate a Scenario Summary report and compare the resulting profit values.
5. Create a one-variable Data Table to test selling prices of ₹250, ₹300, ₹350, ₹400, and ₹450.
6. Create a two-variable Data Table using different selling prices and sales volumes.
7. Explain why a fixed-cost model may produce unrealistic results when sales volume changes significantly.
8. Identify a business problem that would be better handled with Solver than Goal Seek.

20. Interview Questions and Answers

Q1. What is What-If Analysis in Excel?

Answer: What-If Analysis is a collection of tools that evaluate how changes in input values affect formula results. It is used for forecasting, budgeting, planning, and decision-making.

Q2. What are the three main What-If Analysis tools?

Answer: The three primary tools are Goal Seek, Scenario Manager, and Data Tables.

Q3. What is Goal Seek used for?

Answer: Goal Seek works backward from a desired formula result to find the required value of one changing input cell.

Q4. Can Goal Seek change multiple input cells simultaneously?

Answer: No. Goal Seek adjusts one changing cell at a time. Solver is more suitable for many optimization problems involving multiple decision variables.

Q5. What is Scenario Manager?

Answer: Scenario Manager stores named sets of input values and lets users switch between them to compare the results of different assumptions.

Q6. How many changing values can one scenario contain?

Answer: A Scenario Manager scenario supports up to 32 changing values.

Q7. What is a Data Table in Excel?

Answer: A Data Table calculates the result of a formula for multiple values of one or two input variables and displays the results together.

Q8. What is the difference between one-variable and two-variable Data Tables?

Answer: A one-variable Data Table tests values for one input cell. A two-variable Data Table tests combinations of values for two input cells.

Q9. What is a Scenario Summary report?

Answer: It is a worksheet report that compares the changing values and selected results of saved scenarios. It does not automatically update when the scenarios change.

Q10. When should Solver be used?

Answer: Solver is useful when an optimization problem requires changing multiple decision variables to maximize or minimize an objective while satisfying constraints.

Q11. Why is What-If Analysis useful for budgeting?

Answer: It allows users to evaluate different assumptions about income and expenses and understand how they affect the budget before making decisions.

Q12. What should be checked before relying on What-If Analysis results?

Answer: Check formula references, input assumptions, units, feasibility constraints, and whether the model reflects the actual business situation.

21. Quick Revision Checklist

• What-If Analysis tests how changing inputs affects formula results.
• Goal Seek finds one input value needed to reach a target result.
• Scenario Manager stores and compares sets of assumptions.
• Data Tables calculate results for one or two changing input variables.
• Solver is a separate add-in for optimization problems involving decision variables and constraints.
• Input cells should be clearly identified and linked to the model formulas.
• Scenario Summary reports need to be regenerated when scenario values change.
• Data Table row and column input cells must match the table orientation.
• Results must be checked for accuracy, feasibility, and realistic assumptions.

22. Conclusion

What-If Analysis is an important Advanced Excel skill that helps users examine uncertainty and compare potential outcomes. Goal Seek works backward from a target, Scenario Manager compares different sets of assumptions, and Data Tables evaluate many possible values for one or two inputs.

By learning these tools, users can build more flexible financial models, perform sales and profit sensitivity analysis, improve budgeting, and support evidence-based business decisions. The most reliable results come from clear assumptions, accurate formulas, and careful validation of the model.

23. Additional Learning Resources

• Microsoft Support: Introduction to What-If Analysis
• Microsoft Support: Use Goal Seek to Find a Result
• Microsoft Support: Switch Between Scenarios
• Microsoft Support: Calculate Multiple Results with a Data Table
• Explore the Advanced Excel Course at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Official Website